package com.kcwx.system.sync;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlServerJdbcUtils {

	static final String driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 加载JDBC驱动
	// static final String url = "jdbc:sqlserver://127.0.0.1:1433;
	// DatabaseName=DrugTracing";
	static final String url = "jdbc:sqlserver://127.0.0.1:1433; DatabaseName=DrugTracing";
	static final String username = "kcwx";
	static final String password = "Kcwx@2023!";

	Connection conn = null;
	Statement stmt = null;

	public static void main(String[] args) {
		SqlServerJdbcUtils sql = new SqlServerJdbcUtils();
		sql.query("select * from sys_config");
	}

	public SqlServerJdbcUtils() {
		try {
			Class.forName(driverClassName);
			System.out.println("加载数据库驱动成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url, username, password);
			System.out.println("连接数据库驱动成功");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void query(String sql) {
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				String id = rs.getString("config_id");
				// 输出数据
				System.out.print("ID: " + id);
				System.out.print("\n");
			}
			// 完成后关闭
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			} // 什么都不做
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
	}

	public void sync(String sql) {
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			} // 什么都不做
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
	}

	public void insert(String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			System.out.println("插入成功！");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 查询
	public void like() {
		String sql = "select * from hot where name like '%zhang%'";
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			ps = (PreparedStatement) conn.prepareStatement(sql);
			res = ps.executeQuery();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		try {

			while (res.next()) {

				int num = res.getInt(1);
				String name = res.getString(2);
				String author = res.getString(3);
				String style = res.getString(4);
				String form = res.getString(5);

				System.out.println("num: " + num + " ,name: " + name + " ,author: " + author + " ,style: " + style
						+ " ,form: " + form);
				System.out.println("模糊查询成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 更新
	public int update() {
		String sql = "update hot set name = '张军' where num=7";
		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) conn.prepareStatement(sql);
			ps.executeUpdate();
			System.out.println("更新成功！");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	// 删除
	public void delete() {
		String sql = "delete from hot where num = 1";
		String sql2 = "delete from hot where num = 2";
		String sql3 = "delete from hot where num = 3";
		String sql4 = "delete from hot where num = 4";
		String sql5 = "delete from hot where num = 5";

		PreparedStatement ps = null;
		try {
			ps = (PreparedStatement) conn.prepareStatement(sql);
			ps.executeUpdate();
			System.out.println("删除成功！");
			ps = (PreparedStatement) conn.prepareStatement(sql2);
			ps.executeUpdate();
			System.out.println("删除成功！");
			ps = (PreparedStatement) conn.prepareStatement(sql3);
			ps.executeUpdate();
			System.out.println("删除成功！");
			ps = (PreparedStatement) conn.prepareStatement(sql4);
			ps.executeUpdate();
			System.out.println("删除成功！");
			ps = (PreparedStatement) conn.prepareStatement(sql5);
			ps.executeUpdate();
			System.out.println("删除成功！");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}